{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 数据预处理与特征工程"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import scipy as sp\n",
    "import matplotlib.pyplot as plt\n",
    "import seaborn as sns\n",
    "from sklearn import preprocessing\n",
    "from sklearn.preprocessing import StandardScaler\n",
    "from collections import Counter\n",
    "import pickle\n",
    "import time"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 1. train数据处理  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dpath = \"./data/\"\n",
    "train = pd.read_csv(dpath+\"train.csv\")\n",
    "train.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "把特征取值较少的类别进行了合并，填补缺失值"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def train_cleaner(input_file,output_file): \n",
    "    \"\"\"\n",
    "    function:\n",
    "        clean train data and write clean train data into output_file\n",
    "    params:\n",
    "        input_file: input file path\n",
    "        output_file: output file path\n",
    "    \"\"\"\n",
    "    fin = open(dpath+input_file,\"r+\")\n",
    "    fout = open(dpath+output_file,\"w+\")\n",
    "    ocolnames = [\"msno\",\"song_id\",\"source_system_tab\",\"source_screen_name\",\"source_type\",\"target\"]\n",
    "    fout.write(\",\".join(ocolnames) + \"\\n\")  \n",
    "    start = 0\n",
    "    for line in fin:\n",
    "        if start == 0:\n",
    "            start+=1\n",
    "            continue\n",
    "        cols = line.strip().split(\",\")\n",
    "        if cols[2] in [\"\",\"notification\",\"settings\"]:\n",
    "            cols[2] = \"other system\"\n",
    "        if cols[3] in [\"Discover New\",\"Search Trends\",\"Search Home\",\\\n",
    "                       \"My library_Search\",\"Self profile more\",\"Concert\",\"Payment\"]:\n",
    "            cols[3] = \"other screen\"\n",
    "        if cols[3] == \"\":\n",
    "            cols[3] = \"screen nan\"\n",
    "        if cols[4] in [\"\",\"topic-article-playlist\",\"artist\",\"my-daily-playlist\"]:\n",
    "            cols[4] = \"other type\"\n",
    "        fout.write(\",\".join(cols)+\"\\n\")\n",
    "    fin.close()\n",
    "    fout.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "类别特征LabelEncoder"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "bug1: '<' not supported between instances of 'str' and 'float'  \n",
    "解决：.astype(str)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def train_encode(input_file):\n",
    "    train_clean = pd.read_csv(dpath+input_file)\n",
    "    le = preprocessing.LabelEncoder()\n",
    "    colnames = [\"source_system_tab\",\"source_screen_name\",\"source_type\"]\n",
    "    for colname in colnames:\n",
    "        if colname == \"source_system_tab\":\n",
    "            cols_2 = le.fit_transform(train_clean[colname].astype(str))\n",
    "        if colname == \"source_screen_name\":\n",
    "            cols_3 = le.fit_transform(train_clean[colname].astype(str))\n",
    "        if colname == \"source_type\":\n",
    "            cols_4 = le.fit_transform(train_clean[colname].astype(str))\n",
    "    return cols_2,cols_3,cols_4"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "train_encode(\"train_clean.csv\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "把处理好的train数据写入文件"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def generate_train_data(input_file,output_file):\n",
    "    ocolnames = [\"msno\",\"song_id\",\"source_system_tab\",\"source_screen_name\",\"source_type\",\"target\"]\n",
    "    fin = open(dpath+input_file,\"r+\")\n",
    "    fout = open(dpath+output_file,\"w+\")\n",
    "    fout.write(\",\".join(ocolnames)+\"\\n\")\n",
    "    i = 0\n",
    "    start = 0\n",
    "    cols_2,cols_3,cols_4 = train_encode(input_file)\n",
    "    for line in fin:\n",
    "        if start == 0:\n",
    "            start+=1\n",
    "            continue\n",
    "        cols = line.strip().split(\",\")\n",
    "        cols[2] = str(cols_2[i])\n",
    "        cols[3] = str(cols_3[i])\n",
    "        cols[4] = str(cols_4[i])\n",
    "        fout.write(\",\".join(cols)+\"\\n\")\n",
    "        i += 1\n",
    "        #文件遍历指针在第7377418行，舍去最后空行\n",
    "        if i == 7377418:\n",
    "            break\n",
    "    fin.close()\n",
    "    fout.close()    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "train_cleaner(\"train.csv\",\"train_clean.csv\")\n",
    "generate_train_data(\"train_clean.csv\",\"train_data.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "train_data = pd.read_csv(dpath+\"train_data.csv\")\n",
    "train_data.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2.test数据处理"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def test_cleaner(input_file,output_file): \n",
    "    \"\"\"\n",
    "    function:\n",
    "        clean test data and write test data into output_file\n",
    "    params:\n",
    "        input_file: input file path\n",
    "        output_file: output file path\n",
    "    \"\"\"\n",
    "    fin = open(dpath+input_file,\"r+\")\n",
    "    fout = open(dpath+output_file,\"w+\")\n",
    "    ocolnames = [\"id\",\"msno\",\"song_id\",\"source_system_tab\",\"source_screen_name\",\"source_type\"]\n",
    "    fout.write(\",\".join(ocolnames) + \"\\n\")  \n",
    "    start = 0\n",
    "    for line in fin:\n",
    "        if start == 0:\n",
    "            start+=1\n",
    "            continue\n",
    "        cols = line.strip().split(\",\")\n",
    "        if cols[3] in [\"\",\"notification\",\"settings\"]:\n",
    "            cols[3] = \"other system\"\n",
    "        if cols[4] in [\"Discover New\",\"Search Trends\",\"Search Home\",\\\n",
    "                       \"My library_Search\",\"Self profile more\",\"Concert\",\"Payment\"]:\n",
    "            cols[4] = \"other screen\"\n",
    "        if cols[4] == \"\":\n",
    "            cols[4] = \"screen nan\"\n",
    "        if cols[5] in [\"\",\"topic-article-playlist\",\"artist\",\"my-daily-playlist\"]:\n",
    "            cols[5] = \"other type\"\n",
    "        fout.write(\",\".join(cols)+\"\\n\")\n",
    "    fin.close()\n",
    "    fout.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def test_encode(input_file):\n",
    "    test_clean = pd.read_csv(dpath+input_file)\n",
    "    le = preprocessing.LabelEncoder()\n",
    "    colnames = [\"source_system_tab\",\"source_screen_name\",\"source_type\"]\n",
    "    for colname in colnames:\n",
    "        if colname == \"source_system_tab\":\n",
    "            cols_2 = le.fit_transform(test_clean[colname].astype(str))\n",
    "        if colname == \"source_screen_name\":\n",
    "            cols_3 = le.fit_transform(test_clean[colname].astype(str))\n",
    "        if colname == \"source_type\":\n",
    "            cols_4 = le.fit_transform(test_clean[colname].astype(str))\n",
    "    return cols_2,cols_3,cols_4"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def generate_test_data(input_file,output_file):\n",
    "    ocolnames = [\"id\",\"msno\",\"song_id\",\"source_system_tab\",\"source_screen_name\",\"source_type\"]\n",
    "    fin = open(dpath+input_file,\"r+\")\n",
    "    fout = open(dpath+output_file,\"w+\")\n",
    "    fout.write(\",\".join(ocolnames)+\"\\n\")\n",
    "    i = 0\n",
    "    start = 0\n",
    "    cols_3,cols_4,cols_5 = test_encode(input_file)\n",
    "    for line in fin:\n",
    "        if start == 0:\n",
    "            start+=1\n",
    "            continue\n",
    "        cols = line.strip().split(\",\")\n",
    "        cols[3] = str(cols_3[i])\n",
    "        cols[4] = str(cols_4[i])\n",
    "        cols[5] = str(cols_5[i])\n",
    "        fout.write(\",\".join(cols)+\"\\n\")\n",
    "        i += 1\n",
    "        #文件遍历指针在第2556790行，舍去最后空行，不然会报错\n",
    "        if i == 2556790:\n",
    "            break\n",
    "    fin.close()\n",
    "    fout.close()    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "test_cleaner(\"test.csv\",\"test_clean.csv\")\n",
    "generate_test_data(\"test_clean.csv\",\"test_data.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "test_data = pd.read_csv(dpath+\"test_data.csv\")\n",
    "test_data.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3.songs数据处理"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "songs = pd.read_csv(dpath+\"songs.csv\")\n",
    "songs.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "songs.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "清洗songs，增加mult_genre特征"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def songs_cleaner(input_file,output_file): \n",
    "    \"\"\"\n",
    "    function:\n",
    "        clean songs data and write songs data into output_file\n",
    "    params:\n",
    "        input_file: input file path\n",
    "        output_file: output file path\n",
    "    \"\"\"\n",
    "    #注意这里的encoding='UTF-8'，不然打开文件报错，为什么？\n",
    "    fin = open(dpath+input_file,\"r+\",encoding='UTF-8')\n",
    "    fout = open(dpath+output_file,\"w+\")\n",
    "    ocolnames = [\"song_id\",\"song_length\",\"genre_ids\",\"language\",\"mult_genre\"]\n",
    "    fout.write(\",\".join(ocolnames) + \"\\n\")\n",
    "    start = 0\n",
    "    for line in fin:\n",
    "        mult_genre = \"0\"\n",
    "        if start == 0:\n",
    "            start+=1\n",
    "            continue\n",
    "        cols = line.strip().split(\",\")\n",
    "        if cols[2] == \"\":\n",
    "            cols[2] = \"1234\"\n",
    "        if \"|\" in cols[2]:\n",
    "            cols[2] = cols[2][0]\n",
    "            mult_genre = \"1\"\n",
    "        #输出列，丢掉cols[3],cols[4],cols[5]\n",
    "        outcols = [cols[0],cols[1],cols[2],cols[6],mult_genre]\n",
    "        fout.write(\",\".join(outcols)+\"\\n\")\n",
    "    fin.close()\n",
    "    fout.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "genre_ids归类，少数类别划分为small_1,small_2,small_3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def merge_category(input_file,output_file):\n",
    "    data = pd.read_csv(dpath+input_file)\n",
    "    small_list1 = []\n",
    "    small_list2 = []\n",
    "    small_list3 = []\n",
    "    feature_count = data[\"genre_ids\"].value_counts()\n",
    "    for i in range(len(feature_count.values)):\n",
    "        if feature_count.values[i]<10000:\n",
    "            small_list1.append(feature_count.index[i])\n",
    "        if feature_count.values[i]>=10000 and feature_count.values[i]<15000:\n",
    "            small_list2.append(feature_count.index[i])\n",
    "        if feature_count.values[i]>=15000 and feature_count.values[i]<20000:\n",
    "            small_list3.append(feature_count.index[i])\n",
    "    #else不能省略，注意这里要赋值给data[\"genre_ids\"],不然未修改原df\n",
    "    data[\"genre_ids\"] = data.genre_ids.apply(lambda x:\"small_1\" if x in small_list1 else x)\n",
    "    data[\"genre_ids\"] = data.genre_ids.apply(lambda x:\"small_2\" if x in small_list2 else x)\n",
    "    data[\"genre_ids\"] = data.genre_ids.apply(lambda x:\"small_3\" if x in small_list3 else x)\n",
    "    data.to_csv(dpath+output_file,index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "songs_clean类别型数据LabelEncoder，连续型做StandardScaler"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def songs_encode(input_file):\n",
    "    songs_clean = pd.read_csv(dpath+input_file)\n",
    "    #song_lenth标准化\n",
    "    ss = StandardScaler()\n",
    "    #不接受1维数组，reshape成二维数组，注意后面写入文件时如何取值\n",
    "    song_length = np.array(songs_clean[\"song_length\"]).reshape(-1,1)\n",
    "    song_length = ss.fit_transform(song_length)\n",
    "    song_length = np.around(song_length,decimals=5)\n",
    "    #genre_ids和language编码\n",
    "    le = preprocessing.LabelEncoder()\n",
    "    colnames = [\"genre_ids\",\"language\"]\n",
    "    for colname in colnames:\n",
    "        if colname == \"genre_ids\":\n",
    "            genre_ids = le.fit_transform(songs_clean[colname].astype(str))\n",
    "        if colname == \"language\":\n",
    "            language = le.fit_transform(songs_clean[colname].astype(str))\n",
    "    return song_length,genre_ids,language"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "把songs清洗好的特征列写入文件"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def generate_songs_data(input_file,output_file):\n",
    "    ocolnames = [\"song_id\",\"song_length\",\"genre_ids\",\"language\",\"mult_genre\"]\n",
    "    fin = open(dpath+input_file,\"r+\")\n",
    "    fout = open(dpath+output_file,\"w+\")\n",
    "    fout.write(\",\".join(ocolnames)+\"\\n\")\n",
    "    i = 0\n",
    "    start = 0\n",
    "    song_length,genre_ids,language = songs_encode(\"songs_clean.csv\")\n",
    "    for line in fin:\n",
    "        if start == 0:\n",
    "            start+=1\n",
    "            continue\n",
    "        cols = line.strip().split(\",\")\n",
    "        #cols_1是(-1,1)二维数组，cols_1[i][0]取出值\n",
    "        cols[1] = str(song_length[i][0])\n",
    "        cols[2] = str(genre_ids[i])\n",
    "        #合并少数language,[2,7,9,6]\n",
    "        if language[i] in [7,9,6]:\n",
    "            language[i] = 2\n",
    "        cols[3] = str(language[i])\n",
    "        fout.write(\",\".join(cols)+\"\\n\")\n",
    "        i += 1\n",
    "        #文件遍历指针在第2296320行，舍去最后空行\n",
    "        if i == 2296320:\n",
    "            break\n",
    "    fin.close()\n",
    "    fout.close()    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "songs_cleaner(\"songs.csv\",\"songs_clean.csv\")\n",
    "merge_category(\"songs_clean.csv\",\"songs_clean.csv\")\n",
    "generate_songs_data(\"songs_clean.csv\",\"songs_data.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "songs_data = pd.read_csv(dpath+\"songs_data.csv\")\n",
    "songs_data.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "songs_data.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "len(songs_data[\"genre_ids\"].unique())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 4.members数据处理"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "members = pd.read_csv(dpath + \"members.csv\")\n",
    "members.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "清洗特征"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def members_encode(members_file):\n",
    "    memmbers = pd.read_csv(dpath+members_file)\n",
    "    bd = members[\"bd\"].apply(lambda x : 0 if x<0 or x>100 else x)\n",
    "    bd = pd.cut(bd.values,bins=[-1,6,12,18,22,25,30,35,40,50,60,100],labels=False)\n",
    "    #pd.cut输入数据类型要求\n",
    "    registration_init_time = pd.cut(members[\"registration_init_time\"].values,\\\n",
    "                        bins=range(20040000,20190000,10000),labels=False)\n",
    "    expiration_date = members[\"expiration_date\"].apply(lambda\\\n",
    "                  x : 20170930 if x==19700101 else x)\n",
    "    expiration_date = pd.cut(expiration_date.values,\\\n",
    "                        bins=range(20040000,20220000,10000),labels=False)\n",
    "    return bd, registration_init_time, expiration_date"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "把清洗好的特征写入文件"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def generate_members_data(input_file,output_file):\n",
    "    ocolnames = [\"msno\",\"city\",\"bd\",\"gender\",\"registered_via\",\\\n",
    "                 \"registration_init_time\",\"expiration_date\"]\n",
    "    fin = open(dpath+input_file,\"r+\")\n",
    "    fout = open(dpath+output_file,\"w+\")\n",
    "    fout.write(\",\".join(ocolnames)+\"\\n\")\n",
    "    bd,registration_init_time,expiration_date = members_encode(input_file)\n",
    "    i = 0\n",
    "    start = 0\n",
    "    for line in fin:\n",
    "        if start == 0:\n",
    "            start+=1\n",
    "            continue\n",
    "        cols = line.strip().split(\",\")\n",
    "        msno = cols[0]\n",
    "        city = cols[1]\n",
    "        registered_via = cols[4]\n",
    "        if cols[3] == \"female\":\n",
    "            gender = \"0\"\n",
    "        elif cols[3] == \"male\":\n",
    "            gender = \"1\"\n",
    "        else:\n",
    "            gender = \"2\"\n",
    "        if expiration_date[i] == \"\":\n",
    "            expiration_date[i] = 16\n",
    "        outcols = [msno,city,str(bd[i]),gender,registered_via,\\\n",
    "                  str(registration_init_time[i]),str(expiration_date[i])]\n",
    "        fout.write(\",\".join(outcols)+\"\\n\")\n",
    "        i += 1\n",
    "        #文件遍历指针在第34403行，舍去最后空行\n",
    "        if i == 34403:\n",
    "            break\n",
    "    fin.close()\n",
    "    fout.close()    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "generate_members_data(\"members.csv\",\"members_data.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "members_data = pd.read_csv(dpath+\"members_data.csv\")\n",
    "members_data.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 5.生成最终训练和测试文件"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "把songs_data,members_data转化为字典存储  \n",
    "\"song_id\": [\"song_length\",\"genre_ids\",\"language\",\"mult_genre\"]  \n",
    "\"msno\": [\"city\",\"bd\",\"gender\",\"registered_via\",\"registration_init_time\",\"expiration_date\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def generate_info_dict(input_file,output_file):\n",
    "    fin = open(dpath+input_file,\"r+\")\n",
    "    info_dict = dict()\n",
    "    start = 0\n",
    "    for line in fin:\n",
    "        if start == 0:\n",
    "            start+=1\n",
    "            continue\n",
    "        cols = line.strip().split(\",\")\n",
    "        if cols[0] not in info_dict:\n",
    "            info_dict[cols[0]] = []\n",
    "        info_dict[cols[0]] = cols[1:]\n",
    "    pickle.dump(info_dict,open(dpath+output_file,\"wb\"))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "合并train，songs，memmbers数据，生成最终训练文件  \n",
    "train数据集通过歌曲id和用户id映射到songs和members特征"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "dpath = \"./data/\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "def generate_train_merge(input_file,output_file):\n",
    "    songs_dict = pickle.load(open(dpath+\"songs_dict.pkl\",\"rb\"))\n",
    "    members_dict = pickle.load(open(dpath+\"members_dict.pkl\",\"rb\"))\n",
    "    fin = open(dpath+input_file,\"r+\")\n",
    "    fout = open(dpath+output_file,\"w+\")\n",
    "    #注意ocolnames顺序要和后面outcols对应\n",
    "    train_cols = [\"msno\",\"song_id\",\"source_system_tab\",\"source_screen_name\",\"source_type\",\"target\"]\n",
    "    songs_cols = [\"song_length\",\"genre_ids\",\"language\",\"mult_genre\"]\n",
    "    members_cols = [\"city\",\"bd\",\"gender\",\"registered_via\",\"registration_init_time\",\"expiration_date\"]\n",
    "    ocolnames = train_cols[:5]+songs_cols+members_cols+[\"target\"]\n",
    "    fout.write(\",\".join(ocolnames)+\"\\n\")\n",
    "    outcols = []\n",
    "    for line in fin:\n",
    "        cols = line.strip().split(\",\")\n",
    "        #只在训练集或测试集出现的members和songs如何处理\n",
    "        if cols[0] in members_dict and cols[1] in songs_dict:\n",
    "            #合并3组特征并写入文件，注意cols[5]是个str，需要转换成list\n",
    "            outcols = cols[:5]+songs_dict[cols[1]]+members_dict[cols[0]]+[cols[5]]\n",
    "        else:\n",
    "            continue\n",
    "        fout.write(\",\".join(outcols)+\"\\n\")\n",
    "    fout.close()  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "generate_info_dict(\"songs_data.csv\",\"songs_dict.pkl\")\n",
    "generate_info_dict(\"members_data.csv\",\"members_dict.pkl\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "generate_train_merge(\"train_data.csv\",\"train_merge.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "# fin = open(dpath+\"train_data.csv\",\"r+\")\n",
    "# songs_dict = pickle.load(open(dpath+\"songs_dict.pkl\",\"rb\"))\n",
    "# count = 0\n",
    "# for line in fin:\n",
    "#     cols = line.strip().split(\",\")\n",
    "#     if cols[1] in songs_dict:\n",
    "#         count+=1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "7377403"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "count"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>msno</th>\n",
       "      <th>song_id</th>\n",
       "      <th>source_system_tab</th>\n",
       "      <th>source_screen_name</th>\n",
       "      <th>source_type</th>\n",
       "      <th>song_length</th>\n",
       "      <th>genre_ids</th>\n",
       "      <th>language</th>\n",
       "      <th>mult_genre</th>\n",
       "      <th>city</th>\n",
       "      <th>bd</th>\n",
       "      <th>gender</th>\n",
       "      <th>registered_via</th>\n",
       "      <th>registration_init_time</th>\n",
       "      <th>expiration_date</th>\n",
       "      <th>target</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>FGtllVqz18RPiwJj/edr2gV78zirAiY/9SmYvia+kCg=</td>\n",
       "      <td>BBzumQNXUHKdEBOB7mAJuzok+IJA1c2Ryg/yzTF6tik=</td>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "      <td>4</td>\n",
       "      <td>-0.25183</td>\n",
       "      <td>10</td>\n",
       "      <td>8</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>7</td>\n",
       "      <td>8</td>\n",
       "      <td>13</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Xumu+NIjS6QYVxDS4/t3SawvJ7viT9hPKXmf0RtLNx8=</td>\n",
       "      <td>bhp/MpSNoqoxOIB+/l8WPqu6jldth4DIpCm3ayXnJqM=</td>\n",
       "      <td>3</td>\n",
       "      <td>6</td>\n",
       "      <td>3</td>\n",
       "      <td>0.23361</td>\n",
       "      <td>4</td>\n",
       "      <td>8</td>\n",
       "      <td>0</td>\n",
       "      <td>13</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>9</td>\n",
       "      <td>7</td>\n",
       "      <td>13</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Xumu+NIjS6QYVxDS4/t3SawvJ7viT9hPKXmf0RtLNx8=</td>\n",
       "      <td>JNWfrrC7zNN7BdMpsISKa4Mw+xVJYNnxXh3/Epw7QgY=</td>\n",
       "      <td>3</td>\n",
       "      <td>6</td>\n",
       "      <td>3</td>\n",
       "      <td>-0.13422</td>\n",
       "      <td>4</td>\n",
       "      <td>8</td>\n",
       "      <td>0</td>\n",
       "      <td>13</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>9</td>\n",
       "      <td>7</td>\n",
       "      <td>13</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Xumu+NIjS6QYVxDS4/t3SawvJ7viT9hPKXmf0RtLNx8=</td>\n",
       "      <td>2A87tzfnJTSWqD7gIZHisolhe4DMdzkbd6LzO1KHjNs=</td>\n",
       "      <td>3</td>\n",
       "      <td>6</td>\n",
       "      <td>3</td>\n",
       "      <td>0.05294</td>\n",
       "      <td>17</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>13</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>9</td>\n",
       "      <td>7</td>\n",
       "      <td>13</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>FGtllVqz18RPiwJj/edr2gV78zirAiY/9SmYvia+kCg=</td>\n",
       "      <td>3qm6XTZ6MOCU11x8FIVbAGH5l5uMkT3/ZalWG1oo2Gc=</td>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "      <td>4</td>\n",
       "      <td>-0.36784</td>\n",
       "      <td>1</td>\n",
       "      <td>8</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>7</td>\n",
       "      <td>8</td>\n",
       "      <td>13</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                           msno  \\\n",
       "0  FGtllVqz18RPiwJj/edr2gV78zirAiY/9SmYvia+kCg=   \n",
       "1  Xumu+NIjS6QYVxDS4/t3SawvJ7viT9hPKXmf0RtLNx8=   \n",
       "2  Xumu+NIjS6QYVxDS4/t3SawvJ7viT9hPKXmf0RtLNx8=   \n",
       "3  Xumu+NIjS6QYVxDS4/t3SawvJ7viT9hPKXmf0RtLNx8=   \n",
       "4  FGtllVqz18RPiwJj/edr2gV78zirAiY/9SmYvia+kCg=   \n",
       "\n",
       "                                        song_id  source_system_tab  \\\n",
       "0  BBzumQNXUHKdEBOB7mAJuzok+IJA1c2Ryg/yzTF6tik=                  1   \n",
       "1  bhp/MpSNoqoxOIB+/l8WPqu6jldth4DIpCm3ayXnJqM=                  3   \n",
       "2  JNWfrrC7zNN7BdMpsISKa4Mw+xVJYNnxXh3/Epw7QgY=                  3   \n",
       "3  2A87tzfnJTSWqD7gIZHisolhe4DMdzkbd6LzO1KHjNs=                  3   \n",
       "4  3qm6XTZ6MOCU11x8FIVbAGH5l5uMkT3/ZalWG1oo2Gc=                  1   \n",
       "\n",
       "   source_screen_name  source_type  song_length  genre_ids  language  \\\n",
       "0                   5            4     -0.25183         10         8   \n",
       "1                   6            3      0.23361          4         8   \n",
       "2                   6            3     -0.13422          4         8   \n",
       "3                   6            3      0.05294         17         0   \n",
       "4                   5            4     -0.36784          1         8   \n",
       "\n",
       "   mult_genre  city  bd  gender  registered_via  registration_init_time  \\\n",
       "0           0     1   0       2               7                       8   \n",
       "1           0    13   4       0               9                       7   \n",
       "2           0    13   4       0               9                       7   \n",
       "3           0    13   4       0               9                       7   \n",
       "4           0     1   0       2               7                       8   \n",
       "\n",
       "   expiration_date  target  \n",
       "0               13       1  \n",
       "1               13       1  \n",
       "2               13       1  \n",
       "3               13       1  \n",
       "4               13       1  "
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "train_merge = pd.read_csv(dpath+\"train_merge.csv\")\n",
    "train_merge.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(7377403, 16)"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "train_merge.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "collapsed": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "msno                      0\n",
       "song_id                   0\n",
       "source_system_tab         0\n",
       "source_screen_name        0\n",
       "source_type               0\n",
       "song_length               0\n",
       "genre_ids                 0\n",
       "language                  0\n",
       "mult_genre                0\n",
       "city                      0\n",
       "bd                        0\n",
       "gender                    0\n",
       "registered_via            0\n",
       "registration_init_time    0\n",
       "expiration_date           0\n",
       "target                    0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "train_merge.apply(lambda x:sum(x.isnull()))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "合并test，songs，memmbers数据，生成最终测试文件"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def generate_test_final(input_file,output_file):\n",
    "    songs_dict = pickle.load(open(dpath+\"songs_dict.pkl\",\"rb\"))\n",
    "    members_dict = pickle.load(open(dpath+\"members_dict.pkl\",\"rb\"))\n",
    "    fin = open(dpath+input_file,\"r+\")\n",
    "    fout = open(dpath+output_file,\"w+\")\n",
    "    #注意ocolnames顺序要和后面outcols对应\n",
    "    #test多了\"id\",没有\"target\"\n",
    "    test_cols = [\"id\",\"msno\",\"song_id\",\"source_system_tab\",\"source_screen_name\",\"source_type\"]\n",
    "    songs_cols = [\"song_length\",\"genre_ids\",\"language\",\"mult_genre\"]\n",
    "    members_cols = [\"city\",\"bd\",\"gender\",\"registered_via\",\"registration_init_time\",\"expiration_date\"]\n",
    "    ocolnames = test_cols+songs_cols+members_cols\n",
    "    fout.write(\",\".join(ocolnames)+\"\\n\")\n",
    "    outcols = []\n",
    "    for line in fin:\n",
    "        cols = line.strip().split(\",\")\n",
    "        #注意这里cols索引，test多了第一列id，过滤掉只在test中出现的用户，这些用户该如何处理？\n",
    "        if cols[1] in members_dict and cols[2] in songs_dict:\n",
    "            #合并3组特征并写入文件，注意cols[5]是个str，需要转换成list\n",
    "            outcols = cols+songs_dict[cols[2]]+members_dict[cols[1]]\n",
    "        fout.write(\",\".join(outcols)+\"\\n\")\n",
    "    fout.close()  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "generate_test_final(\"test_data.csv\",\"test_final.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "test_final = pd.read_csv(dpath+\"test_final.csv\")\n",
    "test_final.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "test_final.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
